<?php

require_once 'Categories.php';
require_once 'Users.php';

class Images {

    private $_ID;
    private $_title;
    private $_categoryID;
    private $_description;
    private $_width;
    private $_height;
    private $_createdDate;
    private $_modifiedDate;
    private $_status;
    private $_order;
    private $_path;
    private $_createdUserID;
    private $_modifiedUserID;
    private $_createdUser;
    private $_modifiedUser;
    private $_category;

    public function getID() {
        return $this->_ID;
    }

    public function getTitle() {
        return $this->_title;
    }

    public function getCategoryID() {
        return $this->_categoryID;
    }

    public function getDescription() {
        return $this->_description;
    }

    public function getWidth() {
        return $this->_width;
    }

    public function getHeight() {
        return $this->_height;
    }

    public function getCreatedDate() {
        return $this->_createdDate;
    }

    public function getModifiedDate() {
        return $this->_modifiedDate;
    }

    public function getStatus() {
        return $this->_status;
    }

    public function getOrder() {
        return $this->_order;
    }

    public function getPath() {
        return $this->_path;
    }

    public function getCreatedUserID() {
        return $this->_createdUserID;
    }

    public function getModifiedUserID() {
        return $this->_modifiedUserID;
    }

    public function getCreatedUser() {
        return $this->_createdUser;
    }

    public function getModifiedUser() {
        return $this->_modifiedUser;
    }

    public function getCategory() {
        return $this->_category;
    }

    public function setID($ID) {
        $this->_ID = $ID;
    }

    public function setTitle($Title) {
        $this->_title = $Title;
    }

    public function setCategoryID($CategoryID) {
        $this->_categoryID = $CategoryID;
    }

    public function setDescription($Description) {
        $this->_description = $Description;
    }

    public function setWidth($Width) {
        $this->_width = $Width;
    }

    public function setHeight($Height) {
        $this->_height = $Height;
    }

    public function setCreatedDate($CreatedDate) {
        $this->_createdDate = $CreatedDate;
    }

    public function setModifiedDate($ModifiedDate) {
        $this->_modifiedDate = $ModifiedDate;
    }

    public function setStatus($Status) {
        $this->_status = $Status;
    }

    public function setOrder($Order) {
        $this->_order = $Order;
    }

    public function setPath($Path) {
        $this->_path = $Path;
    }

    public function setCreatedUserID($createdUserID) {
        $this->_createdUserID = $createdUserID;
    }

    public function setModifiedUserID($ModifiedUserID) {
        $this->_modifiedUserID = $ModifiedUserID;
    }

    public function setCreatedUser($CreatedUser) {
        $this->_createdUser = $CreatedUser;
    }

    public function setModifiedUser($ModifiedUser) {
        $this->_modifiedUser = $ModifiedUser;
    }

    public function setCategory($Category) {
        $this->_category = $Category;
    }

    public function add($db) {
        if ($db != null) {
            $sql = "INSERT INTO `images`
                                (`I_Title`,
                                 `I_CategoryID`,
                                 `I_Description`,
                                 `I_Width`,
                                 `I_Height`,
                                 `I_CreatedDate`,
                                 `I_ModifiedDate`,
                                 `I_Status`,
                                 `I_Order`,
                                 `I_Path`,
                                 `I_CreatedUserID`,
                                 `I_ModifiedUserID`
                                 )
                    VALUES (:I_Title,
                            :I_CategoryID,
                            :I_Description,
                            :I_Width,
                            :I_Height,
                            :I_CreatedDate,
                            :I_ModifiedDate,
                            :I_Status,
                            :I_Order,
                            :I_Path,
                            :I_CreatedUserID,
                            :I_ModifiedUserID
                            )";

            $parameters = array(
                ':I_Title', ':I_CategoryID',
                ':I_Description', ':I_Width',
                ':I_Height', ':I_CreatedDate',
                ':I_ModifiedDate', ':I_Status',
                ':I_Order', ':I_Path',
                ':I_CreatedUserID', ':I_ModifiedUserID'
            );
            $values = array(
                $this->getTitle(), $this->getCategoryID(),
                $this->getDescription(), $this->getWidth(),
                $this->getHeight(), $this->getCreatedDate(),
                $this->getModifiedDate(), $this->getStatus(),
                $this->getOrder(), $this->getPath(),
                $this->getCreatedUserID(), $this->getModifiedUserID()
            );
            $types = array(
                PDO::PARAM_STR, PDO::PARAM_INT,
                PDO::PARAM_STR, PDO::PARAM_INT,
                PDO::PARAM_INT, PDO::PARAM_STR,
                PDO::PARAM_STR, PDO::PARAM_INT,
                PDO::PARAM_INT, PDO::PARAM_STR,
                PDO::PARAM_INT, PDO::PARAM_INT
            );
            return $db->executeNonQuery($sql, $parameters, $values, $types);
        }
    }

    public function delete($db) {
        if ($db != null) {
            $sql = "DELETE
                    FROM `images`
                    WHERE `I_ID` = :I_ID";

            $parameters = array(':I_ID');
            $values = array($this->getID());
            $types = array(PDO::PARAM_INT);
            return $db->executeNonQuery($sql, $parameters, $values, $types);
        }
    }

    public function update($db) {
        if ($db != null) {
            $sql = "UPDATE `images`
                    SET `I_Title` = :I_Title,
                      `I_CategoryID` = :I_CategoryID,
                      `I_Description` = :I_Description,
                      `I_Width` = :I_Width,
                      `I_Height` = :I_Height,
                      `I_ModifiedDate` = :I_ModifiedDate,
                      `I_Status` = :I_Status,
                      `I_Path` = :I_Path,
                      `I_ModifiedUserID` = :I_ModifiedUserID
                    WHERE `I_ID` = :I_ID;";

            $parameters = array(
                ':I_Title', ':I_CategoryID',
                ':I_Description', ':I_Width',
                ':I_Height', ':I_ModifiedDate',
                ':I_Status',
                ':I_Path',
                ':I_ModifiedUserID',
                ':I_ID'
            );
            $values = array(
                $this->getTitle(), $this->getCategoryID(),
                $this->getDescription(), $this->getWidth(),
                $this->getHeight(), $this->getModifiedDate(),
                $this->getStatus(),
                $this->getPath(),
                $this->getModifiedUserID(),
                $this->getID()
            );
            $types = array(
                PDO::PARAM_STR, PDO::PARAM_INT,
                PDO::PARAM_STR, PDO::PARAM_INT,
                PDO::PARAM_INT, PDO::PARAM_STR,
                PDO::PARAM_INT,
                PDO::PARAM_STR,
                PDO::PARAM_INT,
                PDO::PARAM_INT
            );
            return $db->executeNonQuery($sql, $parameters, $values, $types);
        }
    }

    public function updateStatus($db = null) {
        if ($db != null) {
            $sql = "UPDATE `images`
                    SET `I_Status` = :I_Status
                    WHERE `I_ID` = :I_ID;";
            $parameters = array(':I_Status', ':I_ID');
            $values = array($this->getStatus(), $this->getID());
            $types = array(PDO::PARAM_INT, PDO::PARAM_INT);
            return $db->executeNonQuery($sql, $parameters, $values, $types);
        }
    }

    public function getList($db) {
        if ($db != null) {
            $sql = "SELECT
                    `I_Title`,
                    `I_CategoryID`,
                    `I_Description`,
                    `I_Width`,
                    `I_Height`,
                    `I_CreatedDate`,
                    `I_ModifiedDate`,
                    `I_Status`,
                    `I_Order`,
                    `I_Path`,
                    `I_CreatedUserID`,
                    `I_ModifiedUserID`,
                    `I_ID`
                  FROM `images`
                  ORDER BY I_Order DESC";

            $parameters = null;
            $values = null;
            $types = null;

            $result = $db->executeQuery($sql, $parameters, $values, $types);

            // nếu có kết quả
            if ($result != null) {

                // mảng danh sách trả về
                $rows = array();

                // lấy mỗi dòng dữ liệu đưa vào mảng danh sách trả về
                while ($row = $result->fetch()) {

                    $image = new Images();
                    $image->setID($row->I_ID);
                    $image->setTitle($row->I_Title);
                    $image->setCreatedDate($row->I_CreatedDate);
                    $image->setModifiedDate($row->I_ModifiedDate);
                    $image->setDescription($row->I_Description);
                    $image->setStatus($row->I_Status);
                    $image->setOrder($row->I_Order);
                    $image->setCategoryID($row->I_CategoryID);
                    $image->setWidth($row->I_Width);
                    $image->setHeight($row->I_Height);

                    $image->setCreatedUserID($row->I_CreatedUserID);
                    $image->setModifiedUserID($row->I_ModifiedUserID);


                    // created user
                    $createdUser = new Users();
                    $createdUser->setID($image->getCreatedUserID());
                    $createdUser->getByID($db);
                    $image->setCreatedUser($createdUser);


                    // modified user
                    $modifiedUser = new Users();
                    $modifiedUser->setID($image->getModifiedUserID());
                    $modifiedUser->getByID($db);
                    $image->setModifiedUser($modifiedUser);


                    $category = new Categories();
                    $category->setID($image->getCategoryID());
                    $category = $category->getByID($db);
                    $image->setCategory($category);


                    $rows[] = $image;
                }
                return $rows;
            } else {
                return null;
            }
        }
    }

    public function getListByCategoryID($db) {
        if ($db != null) {

            $sql = "SELECT
                    `I_Title`,
                    `I_CategoryID`,
                    `I_Description`,
                    `I_Width`,
                    `I_Height`,
                    `I_CreatedDate`,
                    `I_ModifiedDate`,
                    `I_Status`,
                    `I_Order`,
                    `I_Path`,
                    `I_CreatedUserID`,
                    `I_ModifiedUserID`,
                    `I_ID`
                  FROM `images`
                  WHERE I_CategoryID = :I_CategoryID
                  ORDER BY I_Order DESC";

            $parameters = array(':I_CategoryID');
            $values = array($this->getCategoryID());
            $types = array(PDO::PARAM_INT);

            $result = $db->executeQuery($sql, $parameters, $values, $types);

            // nếu có kết quả
            if ($result != null) {

                // mảng danh sách trả về
                $rows = array();

                // lấy mỗi dòng dữ liệu đưa vào mảng danh sách trả về
                while ($row = $result->fetch()) {

                    $image = new Images();
                    $image->setID($row->I_ID);
                    $image->setTitle($row->I_Title);
                    $image->setCreatedDate($row->I_CreatedDate);
                    $image->setModifiedDate($row->I_ModifiedDate);
                    $image->setDescription($row->I_Description);
                    $image->setStatus($row->I_Status);
                    $image->setOrder($row->I_Order);
                    $image->setCategoryID($row->I_CategoryID);
                    $image->setWidth($row->I_Width);
                    $image->setHeight($row->I_Height);
                    $image->setPath($row->I_Path);

                    $image->setCreatedUserID($row->I_CreatedUserID);
                    $image->setModifiedUserID($row->I_ModifiedUserID);


                    // created user
                    $createdUser = new Users();
                    $createdUser->setID($image->getCreatedUserID());
                    $createdUser->getByID($db);
                    $image->setCreatedUser($createdUser);


                    // modified user
                    $modifiedUser = new Users();
                    $modifiedUser->setID($image->getModifiedUserID());
                    $modifiedUser->getByID($db);
                    $image->setModifiedUser($modifiedUser);


                    $category = new Categories();
                    $category->setID($image->getCategoryID());
                    $category = $category->getByID($db);
                    $image->setCategory($category);

                    $rows[] = $image;
                }
                return $rows;
            } else {
                return null;
            }
        }
    }

    public function getByID($db = null) {
        if ($db != null) {
            $sql = "SELECT
                        `I_Title`,
                        `I_CategoryID`,
                        `I_Description`,
                        `I_Width`,
                        `I_Height`,
                        `I_CreatedDate`,
                        `I_ModifiedDate`,
                        `I_Status`,
                        `I_Order`,
                        `I_Path`,
                        `I_CreatedUserID`,
                        `I_ModifiedUserID`,
                        `I_ID`
                      FROM `images`
                      WHERE I_ID = :I_ID";

            $parameters = array(':I_ID');
            $values = array($this->getID());
            $types = array(PDO::PARAM_INT);
            $result = $db->executeQuery($sql, $parameters, $values, $types);

            // nếu có kết quả
            if ($result != null) {

                $row = $result->fetch();

                $this->setID($row->I_ID);
                $this->setTitle($row->I_Title);
                $this->setCreatedDate($row->I_CreatedDate);
                $this->setModifiedDate($row->I_ModifiedDate);
                $this->setDescription($row->I_Description);
                $this->setStatus($row->I_Status);
                $this->setOrder($row->I_Order);
                $this->setCategoryID($row->I_CategoryID);
                $this->setWidth($row->I_Width);
                $this->setHeight($row->I_Height);
                $this->setPath($row->I_Path);

                $this->setCreatedUserID($row->I_CreatedUserID);
                $this->setModifiedUserID($row->I_ModifiedUserID);


                // created user
                $createdUser = new Users();
                $createdUser->setID($this->getCreatedUserID());
                $createdUser->getByID($db);
                $this->setCreatedUser($createdUser);


                // modified user
                $modifiedUser = new Users();
                $modifiedUser->setID($this->getModifiedUserID());
                $modifiedUser->getByID($db);
                $this->setModifiedUser($modifiedUser);


                $category = new Categories();
                $category->setID($this->getCategoryID());
                $category = $category->getByID($db);
                $this->setCategory($category);

                return $this;
            } else {
                return null;
            }
        }
    }

    public function getMaxOrder($db) {
        if ($db != null) {
            $sql = "SELECT MAX(I_Order) + 1 AS MaxOrder FROM images";
            $parameters = null;
            $values = null;
            $types = null;
            $result = $db->executeQuery($sql, $parameters, $values, $types);

            $row = $result->fetch();
            if ($row->MaxOrder != null) {
                return $row->MaxOrder;
            } else {
                return 1;
            }
        }
    }

    public function orderUp($db) {
        if ($db != null) {
            // câu lệnh tải currentOrder, nextOrder, nextArticleID
            $sql = "SELECT (SELECT `I_Order` 
                    from images 
                    WHERE I_ID = :I_ID) As `currenOrder`,               
                
                    (SELECT `I_Order` from images WHERE `I_Order` > 
                    (SELECT `I_Order` from images WHERE I_ID = :I_ID) 
                    Order by `I_Order` LIMIT 1) As `nextOrder`,

                    (SELECT I_ID from images WHERE `I_Order` = 
                    (SELECT `I_Order` from images WHERE `I_Order` > 
                    (SELECT `I_Order` from images WHERE I_ID = :I_ID) 
                    Order by `I_Order` LIMIT 1)) As `nextID`";
            $parameters = array(':I_ID');
            $values = array($this->getID());
            $types = array(PDO::PARAM_INT);
            $result = $db->executeQuery($sql, $parameters, $values, $types);



            if ($result != null) {
                $row = $result->fetch();

                if ($row->nextID != null && $row->nextOrder != null) {
                    // cập nhật order của previous article bằng với currentOrder
                    $sqlNextArticle = "update images set `I_Order` = :I_Order where `I_ID` = :I_ID";
                    $parameters2 = array(':I_Order', ':I_ID');
                    $values2 = array($row->currenOrder, $row->nextID);
                    $types2 = array(PDO::PARAM_INT, PDO::PARAM_INT);
                    $db->executeNonQuery($sqlNextArticle, $parameters2, $values2, $types2);

                    // cập nhật order của current article bằng với previous order
                    $sqlCurrentArticle = "update images set `I_Order` = :I_Order where `I_ID` = :I_ID";
                    $parameters3 = array(':I_Order', ':I_ID');
                    $values3 = array($row->nextOrder, $this->getID());
                    $types3 = array(PDO::PARAM_INT, PDO::PARAM_INT);
                    $db->executeNonQuery($sqlCurrentArticle, $parameters3, $values3, $types3);
                }
            }
        }
    }

    function orderDown($db) {
        if ($db != null) {
            // câu lệnh tải currentOrder, previousOrder, previousArticleID
            $sql = "SELECT (SELECT `I_Order` from images 
                WHERE I_ID = :I_ID) As `currenOrder`, 
                    
		(SELECT `I_Order` from images WHERE `I_Order` < 
		(SELECT `I_Order` from images WHERE I_ID = :I_ID) 
		Order by `I_Order` desc LIMIT 1) As `previousOrder`,
                
		(SELECT I_ID from images WHERE `I_Order` = 
		(SELECT `I_Order` from images WHERE `I_Order` < 
		(SELECT `I_Order` from images WHERE I_ID = :I_ID) 
		Order by `I_Order` desc LIMIT 1)) As `previousID`";

            $parameters = array(':I_ID');
            $values = array($this->getID());
            $types = array(PDO::PARAM_INT);

            $result = $db->executeQuery($sql, $parameters, $values, $types);

            if ($result != null) {

                $row = $result->fetch();

                if ($row->previousID != null && $row->previousOrder != null) {
                    // cập nhật order của previous bằng với currentOrder
                    $sqlPreviousArticle = "update images set `I_Order` = :I_Order where `I_ID` = :I_ID";
                    $parameters2 = array(':I_Order', ':I_ID');
                    $values2 = array($row->currenOrder, $row->previousID);
                    $types2 = array(PDO::PARAM_INT, PDO::PARAM_INT);
                    $db->executeNonQuery($sqlPreviousArticle, $parameters2, $values2, $types2);

                    // cập nhật order của current article bằng với previous order
                    $sqlCurrentArticle = "update images set `I_Order` = :I_Order where `I_ID` = :I_ID";
                    $parameters3 = array(':I_Order', ':I_ID');
                    $values3 = array($row->previousOrder, $this->getID());
                    $types3 = array(PDO::PARAM_INT, PDO::PARAM_INT);
                    $db->executeNonQuery($sqlCurrentArticle, $parameters3, $values3, $types3);
                }
            }
        }
    }

}
